package com.sz.common.base.utils;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Package: com.may.boot.test.excel<br>
 * Function: TODO: ADD FUNCTION <br>
 * Author: yanhong.xiao.<br>
 * Date: 2017-11-15 14:53:00.
 */
public class XSSFWorkbookReadUtils {

    public static List<Map<Object, Object>> readExcel(String filePath) throws InvalidFormatException, IOException {
        List<Map<Object, Object>> resultList = Lists.newArrayList();
        // XSSFWorkbook, File
        OPCPackage pkg = OPCPackage.open(new File(filePath));
        XSSFWorkbook wb = new XSSFWorkbook(pkg);
        resultList = readExcel(wb);
        pkg.close();
        return resultList;
    }


    public static List<Map<Object, Object>> readExcelFromStream(InputStream inputStream) throws IOException, InvalidFormatException {
        // XSSFWorkbook, InputStream, needs more memory
        List<Map<Object, Object>> resultList = Lists.newArrayList();
        OPCPackage pkg = OPCPackage.open(inputStream);
        XSSFWorkbook wb = new XSSFWorkbook(pkg);
        resultList = readExcel(wb);
        pkg.close();
        return resultList;

    }

    public static void main(String[] args) throws IOException, InvalidFormatException {
        String filePath = "/Users/aybaylyn/Documents/HNA-2016-17/excels/SanzangOrderModel.xlsx";
        List<Map<Object, Object>> resultList = XSSFWorkbookReadUtils.readExcel(filePath);
        if (!CollectionUtils.isEmpty(resultList)) {
            resultList.forEach(rowmap -> {
                StringBuilder rowData = new StringBuilder();
                rowmap.forEach((k, v) -> {
                    rowData.append(v + ",");
                });
                String rowDataStr = rowData.substring(0, rowData.length() - 1);
                System.out.println(rowDataStr + "-------end");
            });
        }
    }


    public static List<Map<Object, Object>> readExcel(XSSFWorkbook wb) {
        List<Map<Object, Object>> resultList = new ArrayList<>();
        Iterator sheetIterator = wb.sheetIterator();
        List<Sheet> sheetList = Lists.newArrayList(sheetIterator);
        DataFormatter formatter = new DataFormatter();
        sheetList.forEach(sheet -> {
            for (Row row : sheet) {
                Map<Object, Object> rowMap = Maps.newHashMap();
                for (Cell cell : row) {
                    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
                    /**
                     * get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
                     * String text = formatter.formatCellValue(cell);
                     * Alternatively, get the value and format it yourself
                     */
                    switch (cell.getCellTypeEnum()) {
                        case STRING:
                            rowMap.put(cell.getColumnIndex(), cell.getRichStringCellValue().getString());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                rowMap.put(cell.getColumnIndex(), cell.getDateCellValue());
                            } else {
                                rowMap.put(cell.getColumnIndex(), cell.getNumericCellValue());
                            }
                            break;
                        case BOOLEAN:
                            rowMap.put(cell.getColumnIndex(), cell.getBooleanCellValue());
                            break;
                        case FORMULA:
                            rowMap.put(cell.getColumnIndex(), cell.getCellFormula());
                            break;
                        case BLANK:
                            rowMap.put(cell.getColumnIndex(), null);
                            break;
                        default:
                    }
                }
                resultList.add(rowMap);
            }
        });


        return resultList;
    }
}
